package spring.data.specification.service;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;
import spring.data.specification.entity.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Tuple;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述: TODO:
 * 包名: spring.data.specification.service.
 * 作者: barton.
 * 日期: 16-7-2.
 * 项目名称: spring-data
 * 版本: 1.0
 * JDK: since 1.8
 */
@Service
public class AccountService {

  @PersistenceContext
  private EntityManager em;

  /**
   * 演示如何返回List<Tuple> 类型的数据.
   * Tuple是一个元组,表示可以通过Object[]数组的形式访问,也可以通过TupleElement的别名进行访问.
   */
  public List<Tuple> selectAccountByPositionAndStatus(String orgName, String status, String
   regionName, PageRequest page, String searchParam) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Tuple> cq = cb.createTupleQuery();

    // 根(主表)
    Root<SysUser> user = cq.from(SysUser.class);

    // 设置表连接关系
    Join<SysUser, SysOrganization> organization = user.join(SysUser_.sysOrganization, JoinType.LEFT);
    Join<SysUser, SalesMan> salesMan = user.join(SysUser_.salseMan, JoinType.LEFT);
    Join<SalesMan, Region> region = salesMan.join(SalesMan_.region, JoinType.LEFT);
    Join<SysUser, SysRole> role = user.join(SysUser_.sysRoles, JoinType.LEFT);

// 这种方式用在CriteriaQuery<Tuple> 是不可以的.
//    List<Selection> selections = new ArrayList<>();
//
//    selections.add(user.get(SysUser_.username).alias("username"));
//    selections.add(user.get(SysUser_.sysOrganization).get(SysOrganization_.name).alias("name"));

    // 设置要检索的字段
    cq.multiselect(user.get(SysUser_.username).alias("username"),
     organization.get(SysOrganization_.name).alias("oname"),
     user.get(SysUser_.id).alias("userId"),
     salesMan.get(SalesMan_.truename).alias("truename"),
     user.get(SysUser_.status).alias("status"),
     region.get(Region_.name).alias("rname"),
     role.get(SysRole_.id).alias("rid")
    );

    // 设置检索条件
    List<Predicate> conditions = getConditions(orgName, cb, organization, user, status, searchParam, salesMan);

    cq.where(conditions.toArray(new Predicate[conditions.size()]));

    TypedQuery<Tuple> query = em.createQuery(cq);

    return query.getResultList();
  }

  /**
   * 演示如何返回List<Object[]> 类型的数据.
   * 虽然可以在mulselect方法中设置别名,但无法使用.
   */
  public List<Object[]> selectAccountByPositionAndStatus2(String orgName, String status, String
   regionName, PageRequest page, String searchParam) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);

    // 根(主表)
    Root<SysUser> user = cq.from(SysUser.class);

    // 设置表连接关系
    Join<SysUser, SysOrganization> organization = user.join(SysUser_.sysOrganization, JoinType.LEFT);
    Join<SysUser, SalesMan> salesMan = user.join(SysUser_.salseMan, JoinType.LEFT);
    Join<SalesMan, Region> region = salesMan.join(SalesMan_.region, JoinType.LEFT);
    Join<SysUser, SysRole> role = user.join(SysUser_.sysRoles, JoinType.LEFT);

//    这种方式用在CriteriaQuery<Tuple> 是不可以的.
//    List<Selection> selections = new ArrayList<>();
//
//    selections.add(user.get(SysUser_.username).alias("username"));
//    selections.add(user.get(SysUser_.sysOrganization).get(SysOrganization_.name).alias("name"));

    // 设置要检索的字段
    cq.multiselect(user.get(SysUser_.username).alias("username"),
     organization.get(SysOrganization_.name).alias("oname"),
     user.get(SysUser_.id).alias("userId"),
     salesMan.get(SalesMan_.truename).alias("truename"),
     user.get(SysUser_.status).alias("status"),
     region.get(Region_.name).alias("rname"),
     role.get(SysRole_.id).alias("rid")
    );

    // 设置检索条件
    List<Predicate> conditions = getConditions(orgName, cb, organization, user, status, searchParam, salesMan);

    cq.where(conditions.toArray(new Predicate[conditions.size()]));

    TypedQuery<Object[]> query = em.createQuery(cq);

    return query.getResultList();
  }

  /**
   * 演示如何返回List<AssembleEntity> 类型的数据.
   * 将select方法中设置的字段 直接映射到一个实体类中
   * 虽然可以在mulselect方法中设置别名,但无法使用.
   * 注意点:
   * 1.AssembleEntity中必须有和select方法中所这是要检索的字段顺序,类型一致的构造方法
   * 2.跟别名无关
   */
  public List<AssembleEntity> selectAccountByPositionAndStatus3(String orgName, String status, String
   regionName, PageRequest page, String searchParam) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<AssembleEntity> cq = cb.createQuery(AssembleEntity.class);

    // 根(主表)
    Root<SysUser> user = cq.from(SysUser.class);

//    // 设置表连接关系
    Join<SysUser, SysOrganization> organization = user.join(SysUser_.sysOrganization, JoinType.LEFT);
    Join<SysUser, SalesMan> salesMan = user.join(SysUser_.salseMan, JoinType.LEFT);
    Join<SalesMan, Region> region = salesMan.join(SalesMan_.region, JoinType.LEFT);
    Join<SysUser, SysRole> role = user.join(SysUser_.sysRoles, JoinType.LEFT);

    // 设置要检索的字段
    cq.select(cb.construct(AssembleEntity.class,
     user.get("username"),
     organization.get(SysOrganization_.name),
     user.get(SysUser_.id),
     salesMan.get(SalesMan_.truename),
     user.get(SysUser_.status),
     region.get(Region_.name),
     role.get(SysRole_.id)));

    // 设置检索条件
    List<Predicate> conditions = getConditions(orgName, cb, organization, user, status, searchParam, salesMan);

    cq.where(conditions.toArray(new Predicate[conditions.size()]));

    TypedQuery<AssembleEntity> query = em.createQuery(cq);

    return query.getResultList();
  }

  /**
   * in的写法
   */
  private CriteriaBuilder.In getIn(CriteriaBuilder cb, Root<SysUser> root) {
    CriteriaBuilder.In<String> in = cb.in(root.get(SysUser_.status).as(String.class));
    in.value("0");
    in.value("1");
    return in;
  }

  private List<Predicate> getConditions(String orgName, CriteriaBuilder cb, Join<SysUser, SysOrganization> organization,
                                        Root<SysUser> user, String status, String searchParam, Join
                                         salesMan) {
    // 设置检索条件
    List<Predicate> conditions = new ArrayList<>();
    if (StringUtils.isNotBlank(orgName)) {
      if (!StringUtils.startsWith(orgName, "al")) {
        Predicate p;
        if (StringUtils.equals("大区总监", orgName.trim())) {
          p = cb.and(cb.equal(organization.get(SysOrganization_.name).as(String.class), "大区总监"));
        } else {
          p = cb.and(cb.equal(organization.get(SysOrganization_.name).as(String.class), "服务站经理"));
        }
        conditions.add(p);
        conditions.add(getIn(cb, user));
      } else {
        if (!StringUtils.equals("used", status)) {
          conditions.add(cb.and(cb.equal(user.get(SysUser_.status).as(String.class), status)));
        } else {
          conditions.add(getIn(cb, user));
        }
      }

      if (StringUtils.isNotBlank(searchParam)) {
        conditions.add(cb.like(salesMan.get(SalesMan_.truename).as(String.class), "%" + searchParam + "%"));
      }
    }
    return conditions;
  }
}